package com.kichina.galaxy.dev.tools.excel.poi.write;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;


import java.io.FileOutputStream;


/**
 * @author Surging
 * @version 1.0
 * @ClassName ExcelWriteTest
 * @date 2022/5/25 20:51
 * @desc POI 03、07版写入excel测试
 */
public class ExcelWriteTest {

    String PATH = "D:\\project\\personal\\Java\\meta_mall\\a测试文档\\poi\\";

    /*
     * @Author Surging
     * @Description 03版本xls处理 最大行65325 Excel写入
     * @Param
     * @Return void
     * @Date  2022/5/25 20:58
     **/
    @Test
    public void writeTest03() throws Exception {
        //创建一个工作簿
        Workbook wb = new HSSFWorkbook();
        //创建一个工作表
        Sheet sheet = wb.createSheet("Excel写入测试表");
        //创建一个行
        Row row = sheet.createRow(0);
        //创建一个单元格
        Cell cell1 = row.createCell(0);
        cell1.setCellValue("测试数据1");
        Cell cell2 = row.createCell(1);
        cell2.setCellValue(1);
        //创建第二行
        Row row1 = sheet.createRow(1);
        //创建一个单元格
        Cell cell3 = row1.createCell(0);
        cell3.setCellValue("测试数据2");
        Cell cell4 = row1.createCell(1);
        cell4.setCellValue(2);
        //创建第三行
        Row row2 = sheet.createRow(2);
        //创建一个单元格
        Cell cell5 = row2.createCell(0);
        cell5.setCellValue("统计时间");
        Cell cell6 = row2.createCell(1);
        cell6.setCellValue(new DateTime().toString("yyyy-mm-dd HH:mm:SS"));
        FileOutputStream fos = new FileOutputStream(PATH + "Excel测试写入03.xls");
//        FileOutputStream fos = new FileOutputStream(PATH+"Excel测试写入");
        //信息写入
        wb.write(fos);
        //关闭流
        fos.close();
        System.out.println("文件写入完成");
    }

    /*
     * @Author Surging
     * @Description 03版本xlsx处理 无限制行数 Excel写入
     * @Param
     * @Return void
     * @Date  2022/5/25 21:21
     **/
    @Test
    public void writeTest07() throws Exception {
        //创建一个工作簿
        Workbook wb = new XSSFWorkbook();
        //创建一个工作表
        Sheet sheet = wb.createSheet("Excel写入测试表");
        //创建一个行
        Row row = sheet.createRow(0);
        //创建一个单元格
        Cell cell1 = row.createCell(0);
        cell1.setCellValue("测试数据1");
        Cell cell2 = row.createCell(1);
        cell2.setCellValue(1);
        //创建第二行
        Row row1 = sheet.createRow(1);
        //创建一个单元格
        Cell cell3 = row1.createCell(0);
        cell3.setCellValue("测试数据2");
        Cell cell4 = row1.createCell(1);
        cell4.setCellValue(2);
        //创建第三行
        Row row2 = sheet.createRow(2);
        //创建一个单元格
        Cell cell5 = row2.createCell(0);
        cell5.setCellValue("统计时间");
        Cell cell6 = row2.createCell(1);
        cell6.setCellValue(new DateTime().toString("yyyy-mm-dd HH:mm:SS"));
        FileOutputStream fos = new FileOutputStream(PATH + "Excel测试写入07.xlsx");
//        FileOutputStream fos = new FileOutputStream(PATH+"Excel测试写入");
        //信息写入
        wb.write(fos);
        //关闭流
        fos.close();
        System.out.println("文件写入完成");
    }


    /*
     * @Author Surging
     * @Description 03版本xls处理 最大行65325 Excel写入
     * @Param
     * @Return void
     * @Date  2022/5/25 20:58
     **/
    @Test
    public void writeTestBigRow03() throws Exception {
        long timeMillis = System.currentTimeMillis();
        //创建一个工作簿
        Workbook wb = new HSSFWorkbook();
        //创建一个工作表
        Sheet sheet = wb.createSheet("Excel写入测试表");
        //创建一个行
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            //创建一个单元格
            Cell cell1 = row.createCell(0);
            cell1.setCellValue("测试数据1");
            Cell cell2 = row.createCell(1);
            cell2.setCellValue(1);
        }
        FileOutputStream fos = new FileOutputStream(PATH + "ExcelBigRow测试写入03.xls");
        //FileOutputStream fos = new FileOutputStream(PATH+"Excel测试写入");
        //信息写入
        wb.write(fos);
        //关闭流
        fos.close();
        long endMillis = System.currentTimeMillis();
        System.out.println("文件写入完成,所用时间为" + (endMillis - timeMillis));
        //902  1071 1048
    }

    /*
     * @Author Surging
     * @Description 07版本xlsx处理 最大行1048578 Excel写入
     * @Param
     * @Return void
     * @Date  2022/5/25 20:58
     **/
    @Test
    public void writeTestBigRow07() throws Exception {
        long timeMillis = System.currentTimeMillis();
        //创建一个工作簿
        Workbook wb = new XSSFWorkbook();
        //创建一个工作表
        Sheet sheet = wb.createSheet("Excel写入测试表");
        //创建一个行
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            //创建一个单元格
            Cell cell1 = row.createCell(0);
            cell1.setCellValue("测试数据1");
            Cell cell2 = row.createCell(1);
            cell2.setCellValue(1);
        }
        FileOutputStream fos = new FileOutputStream(PATH + "ExcelBigRow测试写入07.xlsx");
        //FileOutputStream fos = new FileOutputStream(PATH+"Excel测试写入");
        //信息写入
        wb.write(fos);
        //关闭流
        fos.close();
        long endMillis = System.currentTimeMillis();
        System.out.println("文件写入完成,所用时间为" + (endMillis - timeMillis));
        //2280
    }

    /*
     * @Author Surging
     * @Description 07版本xlsx处理升级版 最大行1048578 Excel写入
     * @Param
     * @Return void
     * @Date  2022/5/25 20:58
     **/
    @Test
    public void writeTestBigRow07s() throws Exception {
        long timeMillis = System.currentTimeMillis();
        //创建一个工作簿
        Workbook wb = new SXSSFWorkbook();
        //创建一个工作表
        Sheet sheet = wb.createSheet("Excel写入测试表");
        //创建一个行
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            //创建一个单元格
            Cell cell1 = row.createCell(0);
            cell1.setCellValue("测试数据1");
            Cell cell2 = row.createCell(1);
            cell2.setCellValue(1);
        }
        FileOutputStream fos = new FileOutputStream(PATH + "ExcelBigRow测试写入07s.xlsx");
        //FileOutputStream fos = new FileOutputStream(PATH+"Excel测试写入");
        //信息写入
        wb.write(fos);
        //关闭流
        fos.close();
        //清除临时文件
        ((SXSSFWorkbook)wb).dispose();
        long endMillis = System.currentTimeMillis();
        System.out.println("文件写入完成,所用时间为" + (endMillis - timeMillis));
        //742
    }
}
